﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using VSDBM.Code.dto;
using System.Data;
using MySql.Data.MySqlClient;

namespace VSDBM.Code.dao
{
    public class GroupsDAO
    {


        public static String add(Groups group)
        {
            try
            {
                MySqlConnection connection = Connection.getConnection();

                DataSet ds = select(" WHERE description = '" + group.description + "' AND status = 1", "");
                if (ds == null || ds.Tables[0].Rows.Count == 0)
                {
                    MySqlCommand query = new MySqlCommand("INSERT INTO groups (description,userid,status) VALUES ('" + group.description + "',"+group.userid+",1)", connection);
                    query.ExecuteNonQuery();
                    return "Added!";
                }

                return "Group with this description already exist.";
            }
            catch (Exception e)
            {
                return "ERROR! - " + e.Message;
            }
        }

        public static Boolean delete(Groups groups)
        {
            try
            {
                MySqlConnection connection = Connection.getConnection();
                MySqlCommand query2 = new MySqlCommand("DELETE FROM group_sequences WHERE idgroup = " + groups.id + "", connection);
                query2.ExecuteNonQuery();
                MySqlCommand query = new MySqlCommand("DELETE FROM groups WHERE id = " + groups.id + "", connection);
                query.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }

        public static void setActive(int id)
        {
            MySqlConnection connection = Connection.getConnection();
            MySqlCommand query = new MySqlCommand("UPDATE groups SET status=1 WHERE id=" + id, connection);
            try
            {
                query.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static DataSet select(String filters, String orderby)
        {

            DataSet dSet = new DataSet();
            try
            {
                MySqlConnection connection = Connection.getConnection();
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = new MySqlCommand("SELECT * FROM groups " + filters + " " + orderby, connection);
                adapter.Fill(dSet);
                return dSet;
            }
            catch (Exception e)
            {
                return dSet;
            }
        }

        public static List<Sequence> selectSequences(int idGroup)
        {
            List<Sequence> SequenceList = new List<Sequence>();
            int seqid = 0;
            DataSet dSet = new DataSet();
            MySqlConnection connection = Connection.getConnection();
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            adapter.SelectCommand = new MySqlCommand("SELECT * FROM group_sequences WHERE idgroup="+idGroup, connection);
            adapter.Fill(dSet);
            foreach(DataRow DR in dSet.Tables[0].Rows)
            {
                seqid = Convert.ToInt32(DR["idsequence"]);
                foreach(Sequence seq in SequenceDAO.DStoList(SequenceDAO.select("WHERE id="+seqid, "")))
                {
                    SequenceList.Add(seq);
                }
            }
            return SequenceList;
        }
        public static void addSequence(int idgroup, int idsequence)
        {
            try
            {
                 MySqlConnection connection = Connection.getConnection();
                 MySqlCommand query = new MySqlCommand("INSERT INTO group_sequences (idsequence,idgroup) VALUES (" + idsequence + "," + idgroup + ")", connection);
                 query.ExecuteNonQuery();

            }
            catch (Exception)
            {
                
                throw;
            }
        }


    }
}